' grant_sql_perms.vbs
'
' Grants appropriate SQL permissions for the ECTS tables to the service account
'
' Takes two arguments, the SQL Server and the base service account in the form domain\user
'
' Return Codes:
'    0 - Successful completion
'    1 - Could not find SQL utility.
'    2 - Could not add the permissions
'    3 - Incorrect arguments provided
'

Set objFSO = Wscript.CreateObject("Scripting.FileSystemObject")
Set objShell = Wscript.CreateObject("Wscript.Shell")
Set colProcessEnvVars = objShell.Environment("Process")

'
' Parse arguments
'

Set args = Wscript.Arguments
If args.Count < 2 Then
    DisplaySyntax
    Wscript.Quit(3)
End If
    
strSqlServer = args.Item(0)
strUserid = args.Item(1)

'
' Set up file names
'

strOSQL = objFSO.BuildPath(colProcessEnvVars("ProgramFiles"), "\Microsoft SQL Server\90\Tools\binn\osql.exe")

'
' Ensure that all files are where they need to be
'

if Not objFSO.FileExists(strOSQL) Then
    Wscript.Echo("ERROR: Can't find " + strOSQL + ". Is SQL Server 2005 installed?")
    Wscript.Quit(1)
End If

'
' Create the SQL command file
'

strFileName = objFSO.GetTempName
strTempDir = colProcessEnvVars("TEMP")
strCommandFileName = objFSO.BuildPath(strTempDir, strFileName)
Set objCommandFile = objFSO.CreateTextFile(strCommandFileName)

objCommandFile.writeLine("USE [ECTS];")
objCommandFile.writeLine("CREATE USER [" + strUserid + "] FOR LOGIN [" + strUserid + "]")
objCommandFile.writeLine("GO")
objCommandFile.writeLine("EXEC sp_addrolemember 'db_datareader', '" + strUserid + "'")
objCommandFile.writeLine("GO")
objCommandFile.writeLine("EXEC sp_addrolemember 'db_datawriter', '" + strUserid + "'")
objCommandFile.writeLine("GO")
objCommandFile.Close

'
' Echo input parameters
'

Wscript.Echo("Running: cscript grant_sql_perms.vbs " + strSqlServer + " " + strUserid)
Wscript.Echo("")

'
' Add the permissions
'

Wscript.Echo("Adding the permissions...")

intReturn = objShell.Run("""" + strOSQL + """ -S " + strSqlServer + " -E -b -i " + strCommandFileName, 7, True)

' objFSO.DeleteFile(strCommandFileName)

If intReturn = 0 Then
    Wscript.Echo("Done!")
    Wscript.Echo("")
Else
    Wscript.Echo("ERROR: Adding permissions did not complete successfully.  Script aborting...")
    Wscript.Echo("")
    Wscript.Quit(2)
End If

Wscript.Echo("Adding permissions complete.")
Wscript.Echo("")

Wscript.Quit(0)

'
' Display the syntax for the script
'

Sub DisplaySyntax
    Wscript.Echo("")
    Wscript.Echo("grant_sql_perms.vbs - Grant SQL permissions for ECTS")
    Wscript.Echo("")
    Wscript.Echo("Usage: cscript grant_sql_perms.vbs SQL_server domain_user")
    Wscript.Echo("")
    Wscript.Echo("Where: ""SQL_server""   is the SQL Server name, such as TREY\SQLEXPRESS")
    Wscript.Echo("       ""domain_user""  is the privileged domain account that needs")
    Wscript.Echo("                      access to the ECTS database.")
    Wscript.Echo("")
End Sub
